package cn.com.headfree.depterprela.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import com.gta.edu.sdk.common.dto.Page;

import cn.com.headfree.depterprela.dao.IDeptErpRelaDao;
import cn.com.headfree.entity.TBusinessWorkflowBind;
import cn.com.headfree.entity.TDeptEmpRela;
import cn.com.headfree.entity.TIscRole;
import cn.com.headfree.entity.UserDeptERpRelaMsg;

/**  
 * @Title  DeptErpRelaDaoImpl.java   
 * @Package cn.com.headfree.depterprela.dao.impl   
 * @Description 用户部门Erp关联信息daoImpl
 * @author yangwang 
 * @date  2020年7月3日 下午5:07:39   
 */
@Repository("deptErpRelaDao")
public class DeptErpRelaDaoImpl extends BaseDaoImpl<TDeptEmpRela> implements IDeptErpRelaDao {
	
	/**
	 * 获取部门下所有人员
	 * @param userMsg
	 *  */
	@Override
	public void  getUsersOfDeptPage(Page<UserDeptERpRelaMsg> page,UserDeptERpRelaMsg userMsg) {
		String sql = "select distinct iscu.ID as USER_ID,CONCAT(iscu.NAME,'【',IF((iscu.FAX is null),'',CONCAT(iscu.FAX,'|')),IFNULL(tod.name,''),'】') AS USER_NAME,tder.org_id,tder.dept_id,tod.name as DEPT_NAME,tder.erp_no";
    	StringBuilder sb = new StringBuilder(sql);
    	sb.append(" from t_dept_emp_rela tder")
    	.append(" right join isc_user iscu on tder.erp_no=iscu.FAX")
    	.append(" left join t_org_dept tod on tod.id=tder.dept_id")
    	.append(" where tder.org_id=?");
    	
    	List<Object> paramList = new ArrayList<Object>();
    	paramList.add(userMsg.getDeptId());
    	if(StringUtils.isNotBlank(userMsg.getUserName())) {
			sb.append(" and iscu.NAME LIKE concat('%',?,'%') ESCAPE '/'");
            String replaceStr = userMsg.getUserName().replaceAll("%", "/%").replaceAll("_", "/_");
            paramList.add(replaceStr);
		}
    	
    	this.findPageBySql(UserDeptERpRelaMsg.class, sb.toString(), page, paramList.toArray());
	}
	
	/**
	 * 获取人员erp编码信息
	 * @param userIds
	 * @return List<UserDeptERpRelaMsg>
	 *  */
	@Override
	public List<UserDeptERpRelaMsg> getErpNoByUserIds(String userIds){
		String sql = "select distinct id as user_id,name as user_name,fax as erp_no,BASEORG_ID as dept_id";
    	StringBuilder sb = new StringBuilder(sql);
    	sb.append(" from isc_user")
    	.append(" where ");
		
    	List<Object> paramList = new ArrayList<Object>();
		if(StringUtils.isNotBlank(userIds)) {
			String[] idArr = userIds.split(",");
			if((null != idArr)&&(0 < idArr.length)) {
				Boolean bool = false;
				for(int i=0;i<idArr.length;i++) {
					if(StringUtils.isNotBlank(idArr[i])) {
						if(bool) {
							sb.append(" or id=?");
						}else {
							sb.append(" id=?");
						}
						bool = true;
						paramList.add(idArr[i]);
					}
				}
				if(bool) {
					return this.findAllBySQL(UserDeptERpRelaMsg.class, sb.toString(), null, false, paramList.toArray());
				}
			}
		}
		return null;
	}
	

	/**
	 * 新增角色信息
	 * @param role
	 *  */
	@Override
	public void insertIscRole(TIscRole role) {
		String sql = "insert into t_isc_role(id,name,code) values";
    	StringBuilder sb = new StringBuilder(sql);
		sb.append("(#{id,jdbcType=VARCHAR},")
		.append("#{name,jdbcType=VARCHAR},")
		.append("#{code,jdbcType=VARCHAR})");
		
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("id", role.getId());
        params.put("name", role.getName());
        params.put("code", role.getCode());
        
        this.executeSql(sb.toString(), params);
	}

	/**
	 * 新增角色信息
	 * @param roleList
	 * @throws SQLException 
	 *  */
	@Override
	public void insertIscRole(List<TIscRole> roleList) throws SQLException {
		if((null != roleList)&&(0 < roleList.size())) {
			String sql = "insert into t_isc_role(id,name,code) values(?,?,?)";
			String[][] roleArr = new String[roleList.size()][3];
			for(int i=0;i<roleList.size();i++) {
				roleArr[i][0]=roleList.get(i).getId();
				roleArr[i][1]=roleList.get(i).getName();
				roleArr[i][2]=roleList.get(i).getCode();
			}
	        
	        this.executeBatch(sql, roleArr);
		}
	}
	
	/**
	 * 编辑角色信息
	 * @param role
	 *  */
	@Override
	public void editIscRole(TIscRole role){
		String sql = "update t_isc_role set ";
    	StringBuilder sb = new StringBuilder(sql);
		sb.append("name=#{name,jdbcType=VARCHAR},")
		.append("code=#{code,jdbcType=VARCHAR}")
		.append(" where id=#{id,jdbcType=VARCHAR}");
		
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("id", role.getId());
        params.put("name", role.getName());
        params.put("code", role.getCode());
        
        this.executeSql(sb.toString(), params);
	}
	

	/**
	 * 编辑角色信息
	 * @param roleList
	 * @throws SQLException 
	 *  */
	@Override
	public void editIscRole(List<TIscRole> roleList) throws SQLException {
		if((null != roleList)&&(0 < roleList.size())) {
			String sql = "update t_isc_role set name=?,code=? where id=?";
			String[][] roleArr = new String[roleList.size()][3];
			for(int i=0;i<roleList.size();i++) {
				roleArr[i][0]=roleList.get(i).getName();
				roleArr[i][1]=roleList.get(i).getCode();
				roleArr[i][2]=roleList.get(i).getId();
			}
	        
	        this.executeBatch(sql, roleArr);
		}
	}
	
	/**
	 * 删除角色信息
	 * @param ids
	 *  */
	@Override
	public void deleteIscRole(String ids){
		if(StringUtils.isNotBlank(ids)) {
			String sql = "delete from t_isc_role ";
			StringBuilder sb=new StringBuilder(sql);
			Map<String,Object> params = new HashMap<String,Object>();
			
			sb.append(" where");
			String[] idArr = ids.split(",");
			if((null != idArr)&&(0 < idArr.length)) {
				Boolean bool = false;
				for(int i=0;i<idArr.length;i++) {
					if(StringUtils.isNotBlank(idArr[i])) {
						if(bool) {
							sb.append(" or id=#{id"+i+"}");
						}else {
							sb.append(" id=#{id"+i+"}");
						}
						bool = true;
						params.put("id"+i, idArr[i]);
					}
				}
				if(bool) {
					this.executeSql(sb.toString(), params);
				}
			}
		}
	}
	
	/**
	 *根据id获取角色信息
	 * @param id
	 * @return TIscRole
	 *  */
	@Override
	public TIscRole getIscRoleById(String id){
		String sql = "select id,name,code";
    	StringBuilder sb = new StringBuilder(sql);
    	sb.append("  from t_isc_role")
    	.append(" where id=?");
    	
    	return this.findUnique(TIscRole.class, sb.toString(), new Object[] {id});
	}

	/**
	 *根据code获取角色信息
	 * @param code
	 * @return List<TIscRole>
	 *  */
	@Override
	public List<TIscRole> getIscRoleByCode(String code) {
		String sql = "select id,name,code";
    	StringBuilder sb = new StringBuilder(sql);
    	sb.append("  from t_isc_role")
    	.append(" where code=?");
    	
    	return this.findAllBySQL(TIscRole.class, sb.toString(), null, false, new Object[] {code});
	}
	
	/**
	 * 获取角色信息
	 * @param ids
	 *  */
	@Override
	public List<TIscRole> getIscRoleList(TIscRole role){
		String sql = "select id,name,code";
    	StringBuilder sb = new StringBuilder(sql);
    	List<Object> paramList = new ArrayList<Object>();
    	
    	sb.append("  from t_isc_role")
    	.append(" where 1=1");
    	if(null != role) {
    		if(StringUtils.isNotBlank(role.getName())) {
    			sb.append(" AND name LIKE concat('%',?,'%') ESCAPE '/'");
                String replaceStr = role.getName().replaceAll("%", "/%").replaceAll("_", "/_");
                paramList.add(replaceStr);
    		}
    		if(StringUtils.isNotBlank(role.getCode())) {
    			sb.append(" AND code LIKE concat('%',?,'%') ESCAPE '/'");
                String replaceStr = role.getCode().replaceAll("%", "/%").replaceAll("_", "/_");
                paramList.add(replaceStr);
    		}
    	}
    	
    	return this.findAllBySQL(TIscRole.class, sb.toString(), null, false, paramList.toArray());
	}

	/**
	 * 获取角色信息
	 * @param bind 
	 * @return TBusinessWorkflowBind
	 *  */
	@Override
	public TBusinessWorkflowBind getBusinessBind(TBusinessWorkflowBind bind) {
		String sql = "select distinct id,org_id,business_type_id,deal_role_id";
    	StringBuilder sb = new StringBuilder(sql);
    	sb.append("  from t_business_workflow_bind")
    	.append(" where business_type_id=?")
    	.append(" and org_id=?");
    	
    	return this.findUnique(TBusinessWorkflowBind.class, sb.toString(), new Object[] {bind.getBusinessTypeId(),bind.getOrgId()});
	}

	@Override
	public List<TDeptEmpRela> getOrgIdByErpNo(String erpNo) {
		String sql = "select t.id,t.erp_no,t.org_id,t.dept_id,d.name as org_name,d.is_head_dept,d.p_id ";
		StringBuilder sb = new StringBuilder(sql);
		sb.append(" from t_dept_emp_rela t ");
		sb.append(" LEFT JOIN t_org_dept d on d.id = t.org_id ");
		sb.append(" where 1=1 ");
		sb.append(" and t.erp_no=?");
		ArrayList<Object> arrayList = new ArrayList<Object>();
		arrayList.add(erpNo);
		return this.findAllBySQL(TDeptEmpRela.class, sb.toString(), null, false, arrayList.toArray());

	}

	@Override
	public List<TDeptEmpRela> getDeptInfoByErpNo(String erpNo) {
		String sql = "select t.id,t.erp_no,t.org_id,t.dept_id,d.name as dept_name ";
		StringBuilder sb = new StringBuilder(sql);
		sb.append(" from t_dept_emp_rela t ");
		sb.append(" LEFT JOIN t_org_dept d on d.id = t.dept_id ");
		sb.append(" where 1=1 ");
		sb.append(" and t.erp_no=?");
		ArrayList<Object> arrayList = new ArrayList<Object>();
		arrayList.add(erpNo);
		return this.findAllBySQL(TDeptEmpRela.class, sb.toString(), null, false, arrayList.toArray());

	}

	@Override
	public List<TDeptEmpRela> getErpNoByOrgId(String orgId) {
		String sql = "select t.id,t.erp_no,t.org_id,t.dept_id ";
		StringBuilder sb = new StringBuilder(sql);
		sb.append(" from t_dept_emp_rela t ");
		sb.append(" where 1=1 ");
		sb.append(" and t.org_id=?");
		ArrayList<Object> arrayList = new ArrayList<Object>();
		arrayList.add(orgId);
		return this.findAllBySQL(TDeptEmpRela.class, sb.toString(), null, false, arrayList.toArray());
	}

	@Override
	public List<TDeptEmpRela> getFullDeptPathByErpNo(String erpNo) {
		String sql = "select o.path ";
		StringBuilder sb = new StringBuilder(sql);
		sb.append(" from t_dept_emp_rela d ");
		sb.append(" LEFT JOIN t_org_dept o on d.dept_id = o.id ");
		sb.append(" where 1=1 ");
		sb.append(" and d.erp_no = ?");
		ArrayList<Object> arrayList = new ArrayList<Object>();
		arrayList.add(erpNo);
		return this.findAllBySQL(TDeptEmpRela.class, sb.toString(), null, false, arrayList.toArray());
	}

	@Override
	public TDeptEmpRela getDeptInfoByDeptId(String deptId) {
		String sql = "select name as dept_name ";
    	StringBuilder sb = new StringBuilder(sql);
    	sb.append("  from t_org_dept ")
    	.append(" where id=?");
    	return this.findUnique(TDeptEmpRela.class, sb.toString(), new Object[] {deptId});
	}

}
